package de.velvettear.musiclister.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Vector;

import de.velvettear.musiclister.util.SettingsHelper;


public class DatabaseHelper {

	private static Connection connect() {
		HashMap<String, String> settings = SettingsHelper.readSettings();
		String host = settings.get(SettingsHelper.HOST).toString();
		String user = settings.get(SettingsHelper.USER).toString();
		String password = settings.get(SettingsHelper.PASSWORD).toString();
		String database = settings.get(SettingsHelper.DATABASE).toString();
		
		Connection connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			connection = DriverManager.getConnection("jdbc:mysql://" + host + "/" + database + "?user=" + user + "&password=" + password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return connection;		
	}
	
	public static ResultSet executeQuery(String query) {
		ResultSet result = null;
		
		try {			
			Connection connection = connect();
			Statement statement = connection.createStatement();
						
			result = statement.executeQuery(query);		
			
//			statement.close();
//			connection.close();			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public static void executeUpdate(String query) {
		try {
			Connection connection = connect();
			Statement statement = connection.createStatement();
									
			statement.execute(query);		
			
			statement.close();
			connection.close();	
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}
	
	private static String cleanText(String text) {
				
		String clean = "";
		String[] parts = text.split("\"");
		for (String part : parts) {
			clean += part + "\\\"";
		}
		clean = clean.substring(0, clean.lastIndexOf("\\"));
		
		parts = clean.split("\'");
		clean = "";
		for (String part : parts) {
			clean += part + "\\\'";
		}
		clean = clean.substring(0, clean.lastIndexOf("\\"));
		
		return clean;
	}
	
	public static Vector<String> getMissingArticles(String[] artists) {
		Vector<String> existingArticles = new Vector<String>();
		Vector<String> missingArticles = new Vector<String>();
		
		try {
			ResultSet result = executeQuery("SELECT title " 
					+ "FROM " + SettingsHelper.getPrefix() + "content");
			
			while (result.next()) {
				existingArticles.add(result.getString(1));
			}
			
			result.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
				
		for (String artist : artists) {
			missingArticles.add(artist);
		}
		
		for (String existing : existingArticles) {
			missingArticles.remove(existing);
		}
		
		return missingArticles;
	}
	
	public static void updateArticle(String title, String introtext) {
		introtext = cleanText(introtext);
		executeUpdate("UPDATE " + SettingsHelper.getPrefix() + "content "
				   + "SET introtext = '" + introtext + "' "
				   + "WHERE title = '" + title + "'");
	}
	
}
